using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;

namespace OccupierArchiver.Helpers
{
    public class SqlScriptExporter
    {
        private readonly DataTable _dataTable;
        public string FileName { get; set; }

        public SqlScriptExporter(DataTable dataTable)
        {
            _dataTable = dataTable;
        }

        public void BuildExportScript(StreamWriter stream)
        {
            var sb = new StringBuilder();
            var ins = new StringBuilder();
            var cols = new List<string>();
            var bFirst = true;

            if (_dataTable.DataSet != null &&
            !String.IsNullOrWhiteSpace(_dataTable.DataSet.DataSetName))
            {
                sb.AppendLine("USE " + _dataTable.DataSet.DataSetName);
                sb.AppendLine("GO");
                sb.AppendLine();
                stream.Write(sb.ToString());
                sb.Clear();
            }

            ins.Append("INSERT INTO [" + _dataTable.TableName + "] (");
            foreach (DataColumn column in _dataTable.Columns)
            {
                if (!column.AutoIncrement)
                {
                    if (bFirst)
                        bFirst = false;
                    else
                    {
                        ins.Append(", ");
                    }
                    ins.Append("[" + column.ColumnName + "]");
                    cols.Add(column.ColumnName);
                }
            }
            ins.AppendLine(") ");
            ins.Append("VALUES ");
            for (int i = 0; i < _dataTable.Rows.Count; i++)
            {
                var vals = new List<string>();
                foreach (var col in cols)
                {
                    var value = _dataTable.Rows[i][col].ToString();
                    vals.Add("N'" + FormatFactory.MSSQLEscape(value) + "'");
                }
                if (i % 1000 == 0)
                {
                    sb.AppendLine(ins.ToString());
                    sb.Append("(" + String.Join(",", vals) + ")");
                    stream.Write(sb.ToString());
                    sb.Clear();
                }
                else
                {
                    sb.AppendLine(",");
                    sb.Append("(" + String.Join(",", vals) + ")");
                }
            }
            sb.AppendLine();
            sb.AppendLine("GO");
            sb.AppendLine();
            stream.Write(sb.ToString());
            sb.Clear();
            stream.Close();
        }
    }
}
